IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sprSelect_SearchNames')
	BEGIN
		DROP  Procedure  sprSelect_SearchNames
	END

GO

CREATE Procedure sprSelect_SearchNames
	@searchText nvarchar(200),
	@anywhereInText bit
AS

	declare @searchLen int
	set @searchText = lower(ltrim(rtrim(@searchText))) + '%'
	if (@anywhereInText = 1) set @searchText = '%' + @searchText 
	
	declare @recs table(NameId uniqueidentifier, counter int identity)
	insert @recs
	select NameId
	from [Name]
	where lower(NameFull) like @searchText
				
	
	select cast(n.NameId as varchar(38)) as NameId, 
		n.NameLSID, 
		n.NameFull, 
		n.Rank, 
		n.RankId, 
		cast(n.ParentNameId as varchar(38)) as ParentNameId,
		n.ParentName,
		cast(n.PreferredNameId as varchar(38)) as PreferredNameId,
		n.PreferredName,
		n.Canonical, 
		n.Authors, 
		n.BasionymAuthors, 
		n.CombinationAuthors, 
		n.PublishedIn, 
		cast(n.ReferenceId as varchar(38)) as ReferenceId, 
		n.Year, 
		n.MicroReference, 
		n.TypeVoucher, 
		n.TypeName, 
		cast(n.TypeNameId as varchar(38)) as TypeNameId, 
		n.Orthography, 
		n.Basionym, 
		cast(n.BasionymId as varchar(38)) as BasionymId, 
		n.BasedOn, 
		cast(n.BasedOnId as varchar(38)) as BasedOnId, 
		n.ConservedAgainst, 
		cast(n.ConservedAgainstId as varchar(38)) as ConservedAgainstId, 
		n.HomonymOf, 
		cast(n.HomonymOfId as varchar(38)) as HomonymOfId, 
		n.ReplacementFor, 
		cast(n.ReplacementForId as varchar(38)) as ReplacementForId,  
		BlockingName, 
		cast(BlockingNameId as varchar(38)) as BlockingNameId,
		n.InCitation,
		n.Invalid, 
		n.Illegitimate, 
		n.Misapplied, 
		n.ProParte, 
		n.NomNotes, 
		n.StatusNotes,
		n.Notes,
		n.ChangeDate, 
		n.ChangeUser
	from Name n
	inner join @recs r on r.NameId = n.NameId

GO


GRANT EXEC ON sprSelect_SearchNames TO PUBLIC

GO


